package com.rdc.example.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import static com.rdc.example.util.DBUtil.closeResources;
import static com.rdc.example.util.DBUtil.getConnection;

public class PlayListSongDaoImpl implements com.rdc.example.dao.PlayListSongDao {

    @Override
    public boolean insert(Integer playListId, Integer songId) throws Exception {
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {// 获取连接
            conn = getConnection();
            String sql = "insert into `play_list_song` (play_list_id,song_id) values (?,?);";
            // 执行 sql 语句
            pstmt = conn.prepareStatement(sql);
            // 设值
            pstmt.setInt(1, playListId);
            pstmt.setInt(2, songId);
            // 以影响行数 row 判断并返回插入结果
            int row = pstmt.executeUpdate();
            return row > 0;
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            // 释放资源
            closeResources(null, pstmt, conn);
        }
    }

    @Override
    public boolean delete(Integer playListId, Integer songId) throws Exception {
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {// 获取连接
            conn = getConnection();
            String sql = "delete from `play_list_song` where play_list_id =? and song_id =?";
            // 执行 sql 语句
            pstmt = conn.prepareStatement(sql);
            // 设值
            pstmt.setInt(1, playListId);
            pstmt.setInt(2, songId);
            // 以影响行数 row 判断并返回插入结果
            int row = pstmt.executeUpdate();
            return row > 0;
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            // 释放资源
            closeResources(null, pstmt, conn);
        }
    }

    @Override
    public boolean check(Integer playListId, Integer songId) throws Exception {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            // 获取连接
            conn = getConnection();
            // 定义查询语句 查询有没有时select 1 比count 更快
            String sql = "select 1 from `play_list_song` where play_list_id =? and song_id =?";
            pstmt = conn.prepareStatement(sql);
            // 设置参数
            pstmt.setInt(1, playListId);
            pstmt.setInt(2, songId);
            // 执行查询操作
            rs = pstmt.executeQuery();
            // 如果结果集有下一行，说明存在记录，即用户已点赞
            return rs.next();
        } finally {
            // 释放资源
            closeResources(rs, pstmt, conn);
        }
    }

    @Override
    public boolean deleteAllByPlayListId(Integer playListId) throws Exception {
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {// 获取连接
            conn = getConnection();
            String sql = "delete from `play_list_song` where play_list_id =? ";
            // 执行 sql 语句
            pstmt = conn.prepareStatement(sql);
            // 设值
            pstmt.setInt(1, playListId);
            // 不检查影响行数,只要SQL执行无异常，均返回tru
            pstmt.executeUpdate();
            return true;
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            // 释放资源
            closeResources(null, pstmt, conn);
        }
    }

    @Override
    public boolean deleteAllBySongId(Integer songId) throws Exception {
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {// 获取连接
            conn = getConnection();
            String sql = "delete from `play_list_song` where song_id =? ";
            // 执行 sql 语句
            pstmt = conn.prepareStatement(sql);
            // 设值
            pstmt.setInt(1, songId);
            // 不检查影响行数,只要SQL执行无异常，均返回tru
            pstmt.executeUpdate();
            return true;
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            // 释放资源
            closeResources(null, pstmt, conn);
        }
    }

    @Override
    public List<Integer> listSongIdListByPlayListId(Integer playListId) throws Exception {
        Connection conn = getConnection();
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<Integer> songIdList = new ArrayList<>();
        try {
            String sql = "select song_id from `play_list_song` where play_list_id=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, playListId);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                //循环获取结果
                Integer songId = rs.getInt("song_id");
                songIdList.add(songId);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            // 释放资源
            closeResources(rs, pstmt, conn);
        }
        return songIdList;
    }

    @Override
    public List<Integer> listSongIdListOutPlayListId(Integer playListId) throws Exception {
        Connection conn = getConnection();
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<Integer> songIdList = new ArrayList<>();
        try {
            String sql = "select song_id from `play_list_song` where play_list_id != ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, playListId);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                //循环获取结果
                Integer songId = rs.getInt("song_id");
                songIdList.add(songId);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            // 释放资源
            closeResources(rs, pstmt, conn);
        }
        return songIdList;
    }





}
